package com.ruipengkj.service.impl;

import java.io.Serializable;
import java.util.LinkedHashMap;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.ruipengkj.bean.MonthMarkeView;
import com.ruipengkj.commons.base.query.PageModel;
import com.ruipengkj.dao.TMonthMarkeViewDao;
import com.ruipengkj.service.MonthMarkeViewService;

@Service
public class MonthMarkeViewServiceImpl implements MonthMarkeViewService{

	@Resource
	private TMonthMarkeViewDao tMonthMarkeViewDao;

	@Override
	public void deleteService(String id) throws Exception {
		// TODO Auto-generated method stub
		
	}
	//查找月销售汇总
	
	
	@Override
	public List<MonthMarkeView> findDayMonthService(MonthMarkeView entity,
			String stime, String etime) throws Exception {
		String dsql="SET @d:=(YEAR('"+etime+"') - YEAR('"+stime+"')) * 12 + MONTH('"+etime+"') - MONTH('"+stime+"') +1";
		String sql="SELECT * FROM  (SELECT DATE_FORMAT(DATE_ADD('"+stime+"', INTERVAL @d:=@d-1 MONTH), '%Y-%m') AS dtm FROM ruipeng_tb_tuser WHERE @d > 0 LIMIT 35) AS tm " +
				"  LEFT JOIN  (SELECT m, SUM(NeedMoney) AS countMoney, COUNT(orderid)AS number FROM " +
				"  (SELECT NeedMoney, orderid, DATE_FORMAT(happentime, '%Y-%m') AS m FROM ruipeng_tb_torder WHERE HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"'  ) AS torder" +
				"  GROUP BY m) AS torders ON tm.dtm = torders.m LEFT JOIN  (SELECT COUNT(bookid) AS bookNum, m1 FROM" +
				"  (SELECT bookid, DATE_FORMAT(HappenTime, '%Y-%m') AS m1 FROM (SELECT '"+stime+"' AS HappenTime, '' AS bookid) AS tbook WHERE HappenTime >= '"+stime+"' AND HappenTime <'"+etime+"') AS tbooks" +
				"  GROUP BY m1) AS tbooks  ON tm.dtm = tbooks.m1   LEFT JOIN" +
				"  (SELECT COUNT(visitid) AS visitCount, DATE_FORMAT(happentime, '%Y-%m-%d') AS m2 FROM ruipeng_tb_tshopvisit " +
				"  WHERE  HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"') AS tshopvisits  ON tm.dtm = tshopvisits.m2";
		return tMonthMarkeViewDao.querySQL2(sql, MonthMarkeView.class, dsql);
	}
	@Override
	public List<MonthMarkeView> findService(MonthMarkeView entity)
			throws Exception {
		// TODO Auto-generated method stub
		String dsql="SET @d:=(YEAR('2012-07-19') - YEAR('2012-07-01')) * 12 + MONTH('2012-07-19') - MONTH('2012-07-01') +1";
		String sql="SELECT * FROM  (SELECT DATE_FORMAT(DATE_ADD('2012-7-01', INTERVAL @d:=@d-1 MONTH), '%Y-%m') AS dtm FROM ruipeng_tb_tuser WHERE @d > 0 LIMIT 35) AS tm " +
				"  LEFT JOIN  (SELECT m, SUM(NeedMoney) AS countMoney, COUNT(orderid)AS number FROM " +
				"  (SELECT NeedMoney, orderid, DATE_FORMAT(happentime, '%Y-%m') AS m FROM ruipeng_tb_torder WHERE HappenTime >= '2012-7-01' AND HappenTime < '2012-7-19'  ) AS torder" +
				"  GROUP BY m) AS torders ON tm.dtm = torders.m LEFT JOIN  (SELECT COUNT(bookid) AS bookNum, m1 FROM" +
				"  (SELECT bookid, DATE_FORMAT(HappenTime, '%Y-%m') AS m1 FROM (SELECT '2012-7-01' AS HappenTime, '' AS bookid) AS tbook WHERE HappenTime >= '2012-7-01' AND HappenTime <'2012-7-19') AS tbooks" +
				"  GROUP BY m1) AS tbooks  ON tm.dtm = tbooks.m1   LEFT JOIN" +
				"  (SELECT COUNT(visitid) AS visitCount, DATE_FORMAT(happentime, '%Y-%m-%d') AS m2 FROM ruipeng_tb_tshopvisit " +
				"  WHERE  HappenTime >= '2012-7-01' AND HappenTime < '2012-7-19') AS tshopvisits  ON tm.dtm = tshopvisits.m2";
		return tMonthMarkeViewDao.querySQL2(sql, MonthMarkeView.class, dsql);
	}
	

	//查看天销售汇总
	@Override
	public List<MonthMarkeView> findDayService(MonthMarkeView entity,String stime, String etime)
			throws Exception {
		String dsql="SET @d:=(TO_DAYS('"+etime+"') - TO_DAYS('"+stime+"') +1)";
		
		String sql="SELECT * FROM  (SELECT DATE_FORMAT(DATE_ADD('"+stime+"', INTERVAL @d:=@d-1 DAY), '%Y-%m-%d') AS dtm FROM ruipeng_tb_tuser WHERE @d > 0 LIMIT 35) AS tm LEFT JOIN" +
				"  (SELECT m, SUM(NeedMoney) AS countMoney, COUNT(orderid)AS number FROM " +
				"  (SELECT NeedMoney, orderid, DATE_FORMAT(happentime, '%Y-%m-%d') AS m, happentime FROM ruipeng_tb_torder WHERE HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"') AS torder" +
				"  GROUP BY m) AS torders ON tm.dtm = torders.m LEFT JOIN" +
				"  (SELECT COUNT(bookid) AS bookNum, m1 FROM (SELECT bookid, DATE_FORMAT(HappenTime, '%Y-%m-%d') AS m1 FROM (SELECT '"+stime+"' AS HappenTime, '' AS bookid) AS tbook WHERE HappenTime >= '"+stime+"' AND HappenTime  < '"+etime+"' ) AS tbooks" +
				"  GROUP BY m1) AS tbooks ON tm.dtm = tbooks.m1 LEFT JOIN" +
				"  (SELECT COUNT(visitid) AS visitCount, DATE_FORMAT(happentime, '%Y-%m-%d') AS m2 FROM ruipeng_tb_tshopvisit " +
				"  WHERE  HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"' ) AS tshopvisits ON tm.dtm = tshopvisits.m2" ;
		return tMonthMarkeViewDao.querySQL2(sql, MonthMarkeView.class, dsql);
	}
	
	//查看周销售汇总
	@Override
	public List<MonthMarkeView> findWeekService(MonthMarkeView entity,String stime, String etime)
			throws Exception {
		String dsql="SET @d:=FLOOR((TO_DAYS('"+etime+"') - TO_DAYS('"+stime+"'))/7) + 1";
		
		String sql="SELECT * FROM   (SELECT DATE_FORMAT(DATE_ADD('"+stime+"', INTERVAL @d:=@d-1 WEEK), '%Y-%u') AS dtm FROM ruipeng_tb_tuser  WHERE @d > 0 LIMIT 35) AS tm LEFT JOIN" +
				"  (SELECT m, SUM(NeedMoney) AS countMoney, COUNT(orderid) AS number  FROM " +
				"  (SELECT NeedMoney, orderid, DATE_FORMAT(happentime, '%Y-%u') AS m FROM ruipeng_tb_torder  WHERE HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"' ) AS torder" +
				"  GROUP BY m) AS torders ON tm.dtm = torders.m LEFT JOIN   (SELECT COUNT(bookid) AS bookNum, m1 FROM" +
				"  (SELECT bookid, DATE_FORMAT(HappenTime, '%Y-%u') AS m1 FROM (SELECT '"+stime+"' AS HappenTime, '' AS bookid) AS tbook WHERE HappenTime >= '"+stime+"' AND HappenTime <'"+etime+"' ) AS tbooks" +
				"  GROUP BY m1) AS tbooks  ON tm.dtm = tbooks.m1 LEFT JOIN" +
				"  (SELECT COUNT(visitid) AS visitCount, DATE_FORMAT(happentime, '%Y-%m-%d') AS m2 FROM ruipeng_tb_tshopvisit  " +
				"  WHERE  HappenTime >= '"+stime+"' AND HappenTime < '"+etime+"'  ) AS tshopvisits ON tm.dtm = tshopvisits.m2";
		return tMonthMarkeViewDao.querySQL2(sql, MonthMarkeView.class, dsql);
	}

	@Override
	public MonthMarkeView getByIdService(String id) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PageModel<MonthMarkeView> getScrollService(int firstResult,
			int maxResult, MonthMarkeView entity) throws Exception {
		// TODO Auto-generated method stub
		LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
		return null;
	}

	@Override
	public void modifyService(MonthMarkeView entity) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void saveOrUpdateService(MonthMarkeView entity) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Serializable saveRetService(MonthMarkeView entity) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void saveService(MonthMarkeView entity) throws Exception {
		// TODO Auto-generated method stub
		
	}

	public TMonthMarkeViewDao getTMonthMarkeViewDao() {
		return tMonthMarkeViewDao;
	}

	public void setTMonthMarkeViewDao(TMonthMarkeViewDao monthMarkeViewDao) {
		tMonthMarkeViewDao = monthMarkeViewDao;
	}


	
	
}
